﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using BE;

namespace DAL
{
    public class LogDA
    {
        //Define Parameters to use in objectCommand objects
		private const string ParamId = "@id";
		private const string ParamUser = "@user";
		private const string ParamDateTime = "@datetime";
		private const string ParamTypeID = "@typeid";
		private const string ParamEvent = "@event";

		//define name of Database values
		private const string Table = "Log";
		private const string ColId = "ID";
		private const string ColUser = "["+ColGetUser+"]";
        private const string ColGetUser = "User";
		private const string ColDateTime = "DateTime";
		private const string ColTypeId = "TypeId";
		private const string ColEvent = "Event";
		private const string space = ", ";

		private SqlConnection con;

		public LogDA()
		{
			SqlCommands cmd = new SqlCommands();
			con = cmd.GetConnection();
		}

		private Log extractLog( SqlDataReader reader )
		{
			return new Log( reader.GetInt32( reader.GetOrdinal( ColId ) ),
				reader.GetString( reader.GetOrdinal( ColGetUser ) ),
				reader.GetDateTime( reader.GetOrdinal( ColDateTime ) ),
				reader.GetInt32( reader.GetOrdinal( ColTypeId ) ),
				reader.GetString( reader.GetOrdinal( ColEvent ) ) );

		}

		private void setCommand( SqlCommand cmd, Log log )
		{
			cmd.Parameters.AddWithValue( ParamUser, log.User );
			cmd.Parameters.AddWithValue( ParamDateTime, log.Time.ToString("yyyy-MM-dd HH:mm:ss"));
			cmd.Parameters.AddWithValue( ParamTypeID, log.Type );
			cmd.Parameters.AddWithValue( ParamEvent, log.Event );
		}

		public int AddLog( Log log )
		{
			string insertString = "INSERT INTO " + Table + "(" + ColUser + space + ColDateTime + space + ColTypeId + space + ColEvent + ") VALUES(" + ParamUser + space + ParamDateTime + space + ParamTypeID + space + ParamEvent + ")";
			SqlCommand cmd = new SqlCommand( insertString, con );
			setCommand( cmd, log );
			try
			{
				con.Open();

				return cmd.ExecuteNonQuery();
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public Log GetLog( int id )
		{
			string sqlString = "SELECT * FROM " + Table + " WHERE " + ColId + " = " + id;
			SqlCommand cmd = new SqlCommand( sqlString, con );
			SqlDataReader reader;
			try
			{
				con.Open();
				reader = cmd.ExecuteReader();
				reader.Read();
				Log l = extractLog( reader );
				reader.Close();
				return l;
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public List<Log> GetAllLogs()
		{
			string sqlString = "SELECT * FROM " + Table + " order by ID desc";
			SqlCommand cmd = new SqlCommand( sqlString, con );
			SqlDataReader reader;
			try
			{
				con.Open();
				reader = cmd.ExecuteReader();
				List<Log> list = new List<Log>();
				while ( reader.Read() )
					list.Add( extractLog( reader ) );
				reader.Close();
				return list;
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public int EditLog( Log log )
		{
			string sqlString = "UPDATE "+Table+" set ";
			
			sqlString += ColUser + "=" + ParamUser + space;
			sqlString += ColDateTime + "=" + ParamDateTime + space;
            sqlString += ColTypeId + "=" + ParamTypeID + space;
			sqlString += ColEvent + "=" + ParamEvent;
			sqlString += " Where " + ColId +"="+ log.ID;

			SqlCommand cmd = new SqlCommand( sqlString, con );
			setCommand( cmd, log );
			try
			{
				con.Open();
				return cmd.ExecuteNonQuery();
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}

		public int DeleteLog( int id )
		{
			string sqlString = "DELETE FROM " + Table + " WHERE "+ ColId + "="+ id;

			
			SqlCommand cmd = new SqlCommand( sqlString, con );

			try
			{
				con.Open();
				return cmd.ExecuteNonQuery();
			}
			catch
			{
				throw new EasyException( "There was something wrong with the Database Connection" );
			}
			finally
			{
				con.Close();
			}
		}
    }
}
